home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Night Owl 6
/
Night Owl's Shareware - PDSI-006 - Night Owl Corp (1990).iso
/
026a
/
j1_xtab.zip
/
JP1_XTAB.PRG
< prev
Wrap
Text File
|
1990-09-23
|
40KB
|
1,163 lines
** File: JP1_XTAB.PRG
** Authors: Bill Ramos and Kirk Nason, Ashton-Tate
** Modifications: Jay Parsons, CIS 70160,340, A-T BBS Jparsons
** Date: September 23, 1990
** Description: Crosstab function and related functions and
** procedures from the CCBOOSTR.PRG file,
** modified in two respects:
** 1) Support for AVG, STD and VAR tabulations;
** 2) Columns and rows summarized.
** Notes: Two asterisks "**" introduce mods by Jay Parsons (JP).
** All else is original code.
** This file does NOT contain all the functions,
** procedures and macros required to run it. It
** replaces only the following items from CCBOOSTR.PRG:
** FUNCTION Crosstab
** PROCEDURE XT_DisGets
** PROCEDURE Com_XTab
** To use this file, copy CCBOOSTR.PRG to another file,
** delete the named items only from it, append this file,
** declare/compile it with SET PROC TO, and proceed.
** Modifications Copyright (C) 1990, Jay Parsons.
** License is granted to all persons to use these mods for personal purposes.
** License is granted to developers to distribute the compiled code as part
** of dBASE or RunTime custom applications, provided that the developer
** assumes all obligations of support. Distribution of the source or compiled
** code of the modifications for any consideration, whether alone or as part
** of any other product, is otherwise prohibited.
** No warranty of merchantability, fitness for any purpose or otherwise is
** made, and use of the modifications is at the user's sole risk.
** The foregoing statements should not be understood to imply that the author
** of the modifications has title to the original code or any right to permit
** or regulate use or sale of the original code.
FUNCTION CrossTab && UDS for Capturing Parameters for CrossTab
*-----------------------------------------------------------------------
* Capture CrossTab parameters for COM_XTAB
*
* Synopsis:
*
* CROSSTAB( )
*
* Description:
*
* The CrossTab() function displays the front end to capture the
* parameters for the COM_XTAB (compute crosstab) procedure.
* The CrossTab() function is called from within the QBE design
* surface by placing the function CROSSTAB() in any one of the field
* skeletons. CrossTab() will not work within the file pothandle.
*
* The user then fills in the optional title, the column to summarize
* across the top, the column to summarize along the side, the column
* to compute in the middle, and the type of operation (SUM,CNT,MAX,MIN).
** Operations AVG,STD and VAR added. JP.
*
* If the user presses F2 to perform the crosstab, the CrossTab() will
* remove the CROSSTAB() reference from the skeleton and keyboard in
* the commands to transfer to the Applications Panel in the Control
* Center and run EXECCROS.
*
* If the user presses Ctrl-End to save, but not execute, the CrossTab(),
* the CrossTab() function will save the parameters to the CROSSTAB.MEM
* file and clear out the CROSSTAB() reference from the skeletion.
*
* If the user presses Escape to cancel, the CrossTab() will not save
* any of the values and clear out the CROSSTAB() reference from
* the skeletion.
*
* The summary field must be numeric. The top and side fields can be
* any type but memo.
*
* See also:
*
* EXECCROS.PRG Run the CrossTab and display the results
* COM_XTAB.PRG Perform the actual CrossTab computations
*
*-----------------------------------------------------------------------
PRIVATE lc_talk, lc_cursor, lc_display, lc_status, lc_carry, lc_proc,;
ln_typeahd
SAVE SCREEN TO qbe
IF SET("TALK") = "ON"
SET TALK OFF
lc_talk = "ON"
ELSE
lc_talk = "OFF"
ENDIF
lc_cursor = SET("CURSOR")
SET CURSOR ON
lc_escape = SET("ESCAPE")
SET ESCAPE OFF
lc_status = SET("STATUS")
lc_safety = SET("SAFETY")
SET SAFETY OFF
IF TYPE("kn_esc") = "U"
DO key_vars
ENDIF
ll_iscolor = IIF( SET( "COLOR" ) = "ON", .t., .f. )
IF TYPE("SAYCOLOR") = "U"
PUBLIC saycolor
IF ll_iscolor
saycolor = "rg+"
ELSE
saycolor = "w/n"
ENDIF
ENDIF
IF TYPE("FILLCOLOR") = "U"
PUBLIC fillcolor
IF ll_iscolor
fillcolor = "gb+"
ELSE
fillcolor = "n"
ENDIF
ENDIF
IF TYPE("MESSCOLOR") = "U"
PUBLIC messcolor
messcolor = COLOR("MESSAGE")
ln_slash = AT("/", messcolor)
ln_plus = AT("+", messcolor)
IF ln_plus = 0
messcolor = "+" + messcolor
ELSE
IF ln_plus > ln_slash
messcolor = "+" + messcolor
ENDIF
ENDIF
ENDIF
DEFINE WINDOW Crosstab FROM 5,7 TO 18,73 DOUBLE
DO shadowg WITH 5,7,18,73
ACTIVATE WINDOW Crosstab
DO XT_DisSays
DO XT_IniVars
*-- Check to see if a numeric field is present in the DBF
ll_numpres = .F.
ln_fldcnt = 1
DO WHILE .NOT. EMPTY( FIELD( ln_fldcnt ) )
IF TYPE( FIELD( ln_fldcnt ) ) $ "NF"
ll_numpres = .T.
EXIT
ENDIF
ln_fldcnt = ln_fldcnt + 1
ENDDO
IF ll_numpres && Numeric field found
** Next line replaced with expanded options. JP
** gc_actlist = "@M SUM,CNT,MAX,MIN" && Give the full set of options
gc_actlist = "@M SUM,CNT,MAX,MIN,AVG,STD,VAR" && Give full set of options
ELSE
gc_actlist = "@M CNT,MAX,MIN" && Else allow all but SUM
ENDIF
ON KEY LABEL f2 DO XT_F2Exit
ON KEY LABEL shift-f1 DO XT_FldPick WITH VARREAD()
ll_escape = .F.
ll_edit = .T.
ll_f2 = .F.
DO WHILE ll_edit
DO XT_DisGets
READ
ln_readkey = READKEY()
DO CASE
CASE ln_readkey = rn_Esc
ll_escape = .T.
ll_edit = .F.
CASE ln_readkey = rn_CtrlEnd
IF EMPTY( gc_xtop ) .OR. EMPTY( gc_xside ) .OR. EMPTY( gc_xsum )
ll_edit = .T.
ACTIVATE SCREEN
@ 24,0
DO Beep
lc_msg = "The top, side, and summary fields must be specified" + ;
" (Press SPACE)"
@ 24, Center( lc_msg,80 ) SAY lc_msg COLOR &messcolor
ln_tmp = INKEY(0)
@ 24,0
ACTIVATE WINDOW CrossTab
ELSE
ll_edit = .F.
ENDIF
ENDCASE
ENDDO
ON KEY LABEL f2
ON KEY LABEL shift-f1
DO XT_TrmVars && Trim the vars from the form
lc_fullp = SET("FULLPATH")
SET FULLPATH ON
gl_XIsCC = XT_MakExt() && Make EXECCROS.PRG is needed
IF ll_f2
gc_xdbf = DBF() && Capture open DBF file
DO XT_TrmVars && Trim the vars from the form
SAVE TO crosstab ALL LIKE gc_x*
SET INSTRUCT OFF && Prevent extra message when running app
SET SAFETY OFF
IF FILE( "crosstab.qbe" ) && Delete the existing CROSSTAB.QBE file
ERASE crosstab.qbe
ENDIF
RESTORE MACROS FROM ccboostr
PLAY MACRO SaveCros
*-- Keystrokes in SaveCros macro file
* {Ctrl-y}{Enter} && Clear Crosstab() out of skeleton
* {Alt-l}s && Layout menu, Save bar
* {Home}{Ctrl-y} && Clear existing QBE name
* crosstab{Enter} && Save as Crosstab.qbe
* {Ctrl-End} && Exit QBE
* {rightarrow}{rightarrow} && Cursor thru CC to Application panel
* {rightarrow}
* {rightarrow}
* execcros{Enter} && Run ExecCros.PRG
* y && Confirm execution
*-- End of keystroke macro file
ELSE
IF .NOT. ll_escape
ll_edit = .F.
gc_xdbf = DBF()
DO XT_TrmVars && Trim the vars from the form
SAVE TO crosstab ALL LIKE gc_x*
ENDIF
KEYBOARD CHR(kn_CtrlY) && Remove the crosstab()
KEYBOARD CHR(kn_Enter) && Restore skeleton for field
ENDIF
DEACTIVATE WINDOW Crosstab
RELEASE WINDOW Crosstab
RELEASE POPUP crosstab
RELEASE gc_xtitl,gc_xtop,gc_xside,gc_xsum,gc_xoper
RELEASE aa_lookup, params
SET STATUS &lc_status.
SET CURSOR &lc_cursor.
SET ESCAPE &lc_escape.
SET SAFETY &lc_safety.
SET TALK &lc_talk.
SET FULLPATH &lc_fullp
RESTORE SCREEN FROM qbe
RELEASE SCREEN qbe
RETURN("")
*-- EOF: CrossTab()
PROCEDURE XT_DisGets && UDS - CrossTab()
*--------------------------------------------------------------------
* Display the GET fields for the form. Display the Navigation line
* before starting the GET processing. Allow Shift-F1 picklists
* for the field selections.
*--------------------------------------------------------------------
PRIVATE lc_errmsg
lc_errmsg = "Try again"
*-- Perform the GETs
@ 2, 17 GET gc_xtitl PICTURE "@S40";
MESSAGE "Specify an optional heading for the CrossTab display";
WHEN XT_NavDsp( 1 )
@ 4, 34 GET gc_xtop PICTURE "@!S10" ;
MESSAGE "Specify the field to summarize along the top of the CrossTab";
WHEN XT_NavDsp( 2 );
VALID REQUIRED IsInView( gc_xtop ) .AND. TYPE( gc_xtop )<>"M";
ERROR IIF( TYPE(gc_xtop)="M",;
"Memo fields are not allowed with CrossTab",;
"The field specified is not in the current view")
@ 9, 0 GET gc_xside PICTURE "@!S10" ;
MESSAGE "Specify the field to summarize along the side of the CrossTab";
WHEN XT_NavDsp( 3 );
VALID REQUIRED IsInView( gc_xside ) .AND. TYPE( gc_xside )<>"M";
ERROR IIF( TYPE(gc_xside)="M",;
"Memo fields are not allowed with CrossTab",;
"The field specified is not in the current view")
@ 8, 34 GET gc_xsum PICTURE "@!S10" ;
MESSAGE "Specify the field to operate on in the body of the CrossTab";
WHEN XT_NavDsp( 4 );
VALID REQUIRED IsInView( gc_xsum ) .AND. TYPE( gc_xsum )<>"M";
ERROR IIF( TYPE(gc_xsum)="M",;
"Memo fields are not allowed with CrossTab",;
"The field specified is not in the current view")
** Error message below changed to reflect new options. JP
@ 10, 39 GET gc_xoper PICTURE ( gc_actlist ) ;
MESSAGE "Specify the operation on the summary field";
WHEN XT_NavDsp( 5 ) ;
VALID REQUIRED ( TYPE( gc_xsum ) = "C" .AND. ;
gc_xoper <> "SUM" ) .OR. ;
( TYPE( gc_xsum ) = "L" .AND. ;
gc_xoper = "CNT" ) .OR.;
TYPE( gc_xsum ) $ "NFD";
ERROR IIF( TYPE( gc_xsum ) = "C", ;
"SUM, AVG, STD and VAR require numeric fields", ;
"while CNT alone may be used with logical fields")
** "SUM can not be used with character fields", ;
** "MAX, MIN, or SUM can not be used with logical fields")
RETURN
*-- EOP: XT_DisGets
PROCEDURE Com_XTab && Compute CrossTab Based on Parameters
PARAMETERS p_topfld, p_sidfld, p_calfld, p_calc
*------------------------------------------------------------------
* Compute data normalized form of a relational database
* file to a spreadsheet-like structure. It summarizes the
* data in one field by expressing it in terms of two other
* fields.
*
* SYNTAX
*
* DO Com_XTab WITH <top field name>, <side field name>, ;
* <calc field>, <type calc>
*
* where <type calc> can be one of the following functions:
*
* CNT
* MAX
* MIN
* SUM
** Functions added by JP
** AVG && average
** STD && population standard deviation
** VAR && population variance
*
* USAGE
*
* The Com_XTab command handles all records in the active
* file that match any filter condition placed on the data.
* Com_XTab places the result of the CROSSTAB in a file
* named CROSSTAB. Com_XTab will also place the column names
* for the <top field name> in a file named COLUMNS.
* Com_XTab will overwrite these files.
**
** Comments added by JP
**
** When using AVG, STD or VAR, the procedure adds a file,
** N_XTAB.DBF, to hold the number of occurrences of the same
** combination of top and side fields.
**
** When using STD or VAR, the procedure adds a third file,
** SQ_XTAB.DBF, to hold the sum of the squares of the data.
**
** Com_XTab will overwrite these files too.
*
* OPTIONS
*
* CNT counts the records that match the <top field name>
* and the <side field name>.
*
* SUM adds together the values of <calc field> that match
* the <top field name> and the <side field name>.
*
* MAX determines the maximum value of <calc field> for
* matching values of <top field name> and <side field name>.
*
* MIN determines the minimum value of <calc field> for
* matching values of <top field name> and <side field name>.
*
** Comments added by JP
**
** AVG averages the values of <calc field> that match the values
** of <top field name> and <side field name>.
**
** STD calculates the population standard deviation of the values
** of <calc field> that match the <top field name> and <side field
** name>, the square root of VAR.
**
** VAR calculates the population variance of the values of <calc field>
** that match the <top field name> and <side field name>.
**
** If there are no occurrences, the AVG,STD and VAR are (meaninglessly)
** reported as zero to save the time of creating a file with
** character fields, copying the real averages to it as strings
** and filling other fields with blanks. Zero-occurrence fields
** are not counted in the summary totals.
**
* EXAMPLE
*
* Consider the following database file.
*
* CAMPING Name Item Price
* ------- ---- ----- ------
* 1 John Tent 245.00
* 2 Mark Stove 59.00
* 3 Jane Heater 75.00
* 4 Mark Stove 79.00
* 5 Jane Heater 95.00
* 6 Jane Tent 325.00
* 7 John Tent 175.00
* 8 Mark Stove 89.00
*
* DO Com_XTab WITH "Item", "Name", "Price", "SUM"
*
* will create the following two files:
*
* CROSSTAB Name COL01 COL02 COL03
* -------- ---- ------ ------ ------
* 1 Jane 170.00 0.00 325.00
* 2 John 0.00 0.00 420.00
* 3 Mark 0.00 227.00 0.00
*
* The CROSSTAB file will be active in work area 1.
*
* COLUMNS Column TopField
* ------- ------ --------
* 1 COL01 Heater
* 2 COL02 Stove
* 3 COL03 Tent
*
* The COLUMNS file will be open in work area 2. The COLUMNS
* file will have an MDX TAG Column on the Column field.
*
**
** Comments by JP
** As modified, the Crosstab file will also contain a field and a
** record summarizing the data in that row and column, and another
** undisplayed field "SEQ" used to keep it in order for display.
** The N_XTab aand SQ_XTab files, if created, share the structure of
** Crosstab and map directly to it by row and column; they basically
** add a third dimension to hold values needed for their calculations.
**
* COM_XTAB will create a SET FIELDS TO command for each column
* based on the Columns file to get the correct column heading.
* This technique will only work if the resulting column name
* is less than or equal to 10 characters and it contains
* no punctuation or spaces.
*
* You may use this file in the report designer to place a
* meaningfull column label for a report. Before entering
* the report designer execute the command:
* SET FIELDS TO <Enter>
*
* SEE ALSO
*
*------------------------------------------------------------------
*-- Save the current environment
SET FILTER TO && Clear the filter condition if any
PRIVATE lc_talk
IF SET("TALK") = "ON" && Set talk off, but restore it later
SET TALK OFF
lc_talk = "ON"
ELSE
lc_talk = "OFF"
ENDIF
PRIVATE lc_safety
lc_safety = SET("SAFETY") && Save the SAFETY state
SET SAFETY OFF && and turn it off.
PRIVATE lc_excl
lc_excl = SET("EXCLUSIVE") && Save the EXCLUSIVE state
SET EXCLUSIVE ON && and turn it on.
PRIVATE lc_status
lc_status = SET("STATUS") && Save the status bar before turning it off
SET STATUS OFF
SET CURSOR OFF
lc_stat1 = "Determining the number of UNIQUE Records"
lc_stat2 = "Creating CROSSTAB and COLUMNS DBF files "
lc_stat3 = "Populating the COLUMNS file "
lc_stat4 = "Populating the CROSSTAB file "
lc_stat5 = "Creating Pseudo Field Headings "
DEFINE WINDOW com_xtab FROM 5,9 TO 13, 70
DO ShadowG WITH 5, 9, 13, 70
ACTIVATE WINDOW com_xtab
@ 0, 20 SAY "Computing CrossTab"
@ 2, 5 SAY lc_stat1 + + CHR(205) + CHR(205) + CHR(16)
@ 2, 50 SAY "Working"
*-- Make sure a data file is open
IF Empty( ALIAS() )
lc_error = XT_ErrMsg( "No file is in use!", "" )
DO XT_RestEv
CANCEL
ENDIF
*-- Preserve the order of the file going into crosstab
lc_order = ORDER()
** Following 3 routines mostly commented out and replaced by JP due to
** surpassing ugliness of original code.
** Yeah, I know, these guys worked hard and I shouldn't knock it if it
** works. But sometimes, I can't resist.
*-- Strip the alias from the field if it exists
ln_apos = AT( "->", p_topfld ) && Alias position, optional
* IF ln_apos > 0 && Alias exists, now get the field
* lc_ptopfld = SUBSTR( p_topfld, ln_apos + 2, LEN( p_topfld ) - ln_apos )
* ELSE
* lc_ptopfld = UPPER( p_topfld )
* ENDIF
lc_ptopfld = upper(iif(ln_apos=0,p_topfld,substr(p_topfld,ln_apos+2)))
ln_apos = AT( "->", p_sidfld ) && Alias position, optional
* IF ln_apos > 0 && Alias exists, now get the field
* lc_psidfld = SUBSTR( p_sidfld, ln_apos + 2, LEN( p_sidfld ) - ln_apos )
* ELSE
* lc_psidfld = UPPER( p_sidfld )
* ENDIF
lc_psidfld = upper(iif(ln_apos=0,p_sidfld,substr(p_sidfld,ln_apos+2)))
ln_apos = AT( "->", p_calfld ) && Alias position, optional
* IF ln_apos > 0 && Alias exists, now get the field
* lc_pcalfld = SUBSTR( p_calfld, ln_apos + 2, LEN( p_calfld ) - ln_apos )
* ELSE
* lc_pcalfld = UPPER( p_calfld )
* ENDIF
lc_pcalfld = upper(iif(ln_apos=0,p_calfld,substr(p_calfld,ln_apos+2)))
*-- Make sure the fields exist in the current file
*-- Already checked in CrossTab()
*-----------------------------------------------------------------------
NOTE IF .NOT. XT_isfld( lc_ptopfld )
NOTE lc_error = XT_ErrMsg( "Top field: " + lc_ptopfld + ;
NOTE " is not in the file", "" )
NOTE DO XT_RestEv
NOTE RETURN
NOTE ENDIF
NOTE
NOTE IF .NOT. XT_isfld( lc_psidfld )
NOTE lc_error = XT_ErrMsg( "Side field: " + lc_psidfld + ;
NOTE " is not in the file", "" )
NOTE DO XT_RestEv
NOTE RETURN
NOTE ENDIF
NOTE
NOTE IF .NOT. XT_isfld( lc_pcalfld )
NOTE lc_error = XT_ErrMsg( "Calculated field: " + lc_pcalfld + ;
NOTE " is not in the file", "" )
NOTE DO XT_RestEv
NOTE RETURN
NOTE ENDIF
*-----------------------------------------------------------------------
*-- Verify the calculation type
** Types added by JP. Hey! How did AVG get in the original?
* IF .NOT. p_calc $ "SUM,CNT,MAX,MIN,AVG"
IF .NOT. p_calc $ "SUM,CNT,MAX,MIN,AVG,STD,VAR"
lc_error = XT_ErrMsg( "Calculation type: " + p_calc + ;
" is not supported!", "" )
DO XT_RestEv
CANCEL
ENDIF
*-- Compute the number of top and side values
lc_typetop = TYPE( lc_ptopfld )
DO CASE
CASE lc_typetop = "L"
INDEX ON IIF( &lc_ptopfld., "T", "F" ) TAG topfld UNIQUE
CASE lc_typetop = "C"
INDEX ON UPPER( &lc_ptopfld. ) TAG topfld UNIQUE
CASE lc_typetop $ "NF"
INDEX ON &lc_ptopfld. TAG topfld UNIQUE
CASE lc_typetop = "D"
INDEX ON DTOS( &lc_ptopfld. ) TAG topfld UNIQUE
ENDCASE
COUNT TO ln_columns
IF ln_columns > 254
lc_error = XT_ErrMsg( "Unique columns for the top exceeds 254", "")
DELETE TAG topfld
DO XT_RestEv
CANCEL
ENDIF
lc_typesid = TYPE( lc_psidfld )
DO CASE
CASE lc_typesid = "L"
INDEX ON IIF( &lc_psidfld., "T", "F" ) TAG sidfld UNIQUE
CASE lc_typesid = "C"
INDEX ON UPPER( &lc_psidfld. ) TAG sidfld UNIQUE
CASE lc_typesid $ "NF"
INDEX ON &lc_psidfld. TAG sidfld UNIQUE
CASE lc_typesid = "D"
INDEX ON DTOS( &lc_psidfld. ) TAG sidfld UNIQUE
ENDCASE
COUNT TO ln_rows
@ 2, 50 SAY "Complete"
@ 3, 5 SAY lc_stat2 + + CHR(205) + CHR(205) + CHR(16)
@ 3, 50 SAY "Working"
*-- Create the CROSSTAB file to hold the values
COPY TO _cross STRUCTURE EXTENDED
SELECT SELECT()
USE _cross
*-- Make the calc field's attributes global for use in the SET FIELDS TO
*-- command after the crosstab is created to adjust the column size.
IF TYPE( "gn_dec" ) <> "U"
RELEASE gn_dec
ENDIF
PUBLIC gn_dec
IF TYPE( "gn_len" ) <> "U"
RELEASE gn_len
ENDIF
PUBLIC gn_len
*-- Get the attributes for the side field
LOCATE FOR field_name = UPPER( lc_psidfld )
ln_lensid = field_len
ln_decsid = field_dec
*-- Get the attributes for the top field
LOCATE FOR field_name = UPPER( lc_ptopfld )
ln_lentop = field_len
ln_dectop = field_dec
*-- Get the attributes for the calculated column
LOCATE FOR field_name = UPPER( lc_pcalfld )
IF p_calc = "CNT"
gn_len = LEN( ALLTRIM( RECCOUNT() ) )
gn_dec = 0
ELSE
gn_len = field_len
IF p_calc = "SUM" .AND. gn_len < 16 && Adjust column length if SUM
gn_len = gn_len + 2
ENDIF
gn_dec = field_dec
ENDIF
ZAP
*----------------------------------------------------------------------
*-- Create the CROSSTAB file fields
*----------------------------------------------------------------------
*-- Make the Side Column column
APPEND BLANK
** Changed to type "C", length minimum 7 - easier to deal with labels JP
REPLACE field_name WITH lc_psidfld, ;
field_type WITH "C", ;
field_len WITH max( ln_lensid, 7 ) , ;
field_dec WITH 0 , ;
field_idx WITH "N"
* field_type WITH IIF( lc_typesid = "L", "C", lc_typesid ), ;
* field_len WITH ln_lensid) , ;
* field_dec WITH ln_decsid , ;
*-- Make the crosstab columns
ln_col = 1 && Counter for creating columns
DO WHILE ln_col <= ln_columns
lc_colname = "COL" + SUBSTR( STR( 100 + ln_col, 3, 0), 2, 2 )
APPEND BLANK
REPLACE field_name WITH lc_colname, ;
field_type WITH "N" , ;
field_len WITH gn_len , ;
field_dec WITH gn_dec , ;
field_idx WITH "N"
ln_col = ln_col + 1 && Increment column counter
ENDDO
**-- Make the summary column. Added by JP
** If you don't want summary column, take this section out.
APPEND BLANK
lc_rtcol = "Z" + replicate( "_", gn_len - 1 ) && last possible name
REPLACE field_name WITH lc_rtcol, ;
field_type WITH "N" , ;
field_len WITH gn_len,;
field_dec WITH gn_dec,;
field_idx WITH "N"
** Sequence field added to enable renaming bottom record without
** index causing it to move JP - needed only for summaries
APPEND BLANK
REPLACE field_name WITH "Seq", ;
field_type WITH "N", ;
field_len WITH int( log10( ln_rows + 1 ) ) + 1,;
field_dec WITH 0, ;
field_idx WITH "N"
IF p_calc $ "STD,VAR"
COPY TO _cross2
ENDIF
** back to original code
CREATE crosstab FROM _cross
USE crosstab ALIAS crosstab
INDEX ON UPPER( &lc_psidfld ) TAG &lc_psidfld
*----------------------------------------------------------------------
*-- Make the COLUMN file fields
*----------------------------------------------------------------------
SELECT SELECT()
USE _cross
ZAP
APPEND BLANK
REPLACE field_name WITH "COLUMN", ;
field_type WITH "C" , ;
field_len WITH 5 , ;
field_dec WITH 0 , ;
field_idx WITH "Y"
APPEND BLANK
REPLACE field_name WITH lc_ptopfld, ;
field_type WITH "C" , ;
field_len WITH ln_lentop , ;
field_dec WITH 0 , ;
field_idx WITH "N"
CREATE columns FROM _cross
USE columns ALIAS columns
INDEX ON UPPER( &lc_ptopfld ) TAG &lc_ptopfld
@ 3, 50 SAY "Complete"
@ 4, 5 SAY lc_stat3 + + CHR(205) + CHR(205) + CHR(16)
@ 4, 50 SAY "Working"
*-- Fill in the column file with the UNIQUE values for the top fields
*-- from the source DBF
SELECT 1
SET ORDER TO TAG topfld
ln_col = 1
SCAN
lc_colname = "COL" + SUBSTR( STR( 100 + ln_col, 3, 0), 2, 2 )
SELECT columns
APPEND BLANK
DO CASE
CASE lc_typetop = "C"
REPLACE column WITH lc_colname, ;
&lc_ptopfld WITH &p_topfld
CASE lc_typetop = "L"
REPLACE column WITH lc_colname, ;
&lc_ptopfld WITH IIF( &p_topfld., "T", "F" )
CASE lc_typetop $ "NF"
REPLACE column WITH lc_colname, ;
&lc_ptopfld WITH ;
ALLTRIM( STR( &p_topfld, ln_lentop, ln_dectop ) )
CASE lc_typetop = "D"
REPLACE column WITH lc_colname, ;
&lc_ptopfld WITH DTOC( &p_topfld. )
ENDCASE
ln_col = ln_col + 1
SELECT 1
ENDSCAN
*-- Initialize the crosstab matrix by filling in the side column
*-- with the UNIQUE values from the source DBF
*-- This two pass approach is no faster than filling it in as
*-- the crosstab function is populated. It has the added advantage
*-- of correctly ordering the side column.
SELECT 1
SET ORDER TO TAG sidfld
** line added for indexing value
ln_ordno = 1
SCAN
SELECT crosstab
APPEND BLANK
** Type changed to character for use with summaries JP
DO CASE
CASE lc_typesid = "C"
lc_colname = &p_sidfld
CASE lc_typesid = "L"
lc_colname = iif( &p_sidfld., "T", "F" )
CASE lc_typesid $ "NF"
lc_colname = str( &p_sidfld., ln_lensid, ln_decsid )
CASE lc_typesid = "D"
lc_colname = dtos( &p_sidfld ) && we want date order
ENDCASE
REPLACE &lc_psidfld WITH lc_colname
* REPLACE &lc_psidfld WITH ;
* IIF(lc_typesid = "L", ;
* IIF( &p_sidfld, "T", "F" ),;
* &p_sidfld ;
* )
** Also add sequential number for use with summaries
REPLACE Seq WITH ln_ordno
ln_ordno = ln_ordno + 1
** end this change
SELECT 1
ENDSCAN
** add a blank record for summary row, last in order JP
** again, this affects only addition of summary row & col
SELECT crosstab
lc_botrow = "Z" + replicate( "_", ln_lensid - 1 )
APPEND BLANK
REPLACE &lc_psidfld WITH lc_botrow, Seq WITH ln_ordno
** Added by JP -- make auxiliary files like Crosstab, in same order
** Needed for new operations, regardless of summaries in or out
IF p_calc $ "AVG,STD,VAR"
SET ORDER TO
COPY TO N_XTAB
USE N_XTAB ALIAS Number IN select()
INDEX ON UPPER( &lc_psidfld ) TAG &lc_psidfld
SET ORDER TO TAG &lc_psidfld
IF p_calc # "AVG"
** file for squares needs longer fields to hold possible digits
SELECT crosstab
USE _cross2
GOTO 2
SCAN WHILE recno() < reccount() - 1
REPLACE field_len WITH min( gn_len * gn_len, 20 )
ENDSCAN
GO reccount() - 1
REPLACE field_len WITH min( gn_len * gn_len + 2, 20 )
CREATE SQ_XTAB FROM _cross2
APPEND FROM crosstab
USE
USE SQ_XTAB ALIAS Squares
INDEX ON UPPER( &lc_psidfld ) TAG &lc_psidfld
SET ORDER TO TAG &lc_psidfld
USE crosstab ALIAS crosstab IN select()
ENDIF
SELECT crosstab
SET ORDER TO TAG &lc_psidfld
SELECT 1
ENDIF
** end of additions by JP
@ 4, 50 SAY "Complete"
@ 5, 5 SAY lc_stat4 + + CHR(205) + CHR(205) + CHR(16)
@ 5, 50 SAY "Working"
*-- Compute the matrix - brute force
SELECT 1
SET ORDER TO &lc_order && Return to the original order
* && in the event a FOR condition was
* && placed on the index.
SET FILTER TO &gc_filter
SCAN
** Type changed to character JP
DO CASE
CASE lc_typesid = "C"
l_seekval = upper( &lc_psidfld )
CASE lc_typesid = "L"
l_seekval = IIF( &lc_psidfld., "T", "F" )
CASE lc_typesid $ "NF"
l_seekval = str( &lc_psidfld., ln_lensid, ln_decsid )
CASE lc_typesid = "D"
l_seekval = dtos( &p_sidfld )
ENDCASE
* IF lc_typesid = "L"
* l_seekval = IIF( &lc_psidfld., "T", "F" )
* ELSE
* IF lc_typesid = "C"
* l_seekval = UPPER( &lc_psidfld )
* ELSE
* l_seekval = &lc_psidfld
* ENDIF
* ENDIF
** end this change
IF SEEK( l_seekval, "crosstab" )
DO CASE
CASE lc_typetop = "C"
l_seekval = UPPER( &lc_ptopfld )
CASE lc_typetop = "L"
l_seekval = IIF( &lc_ptopfld., "T", "F" )
CASE lc_typetop $ "NF"
l_seekval = ALLTRIM( STR( &lc_ptopfld, ln_lentop, ln_dectop ) )
CASE lc_typetop = "D"
l_seekval = DTOC( &lc_ptopfld )
ENDCASE
IF SEEK( l_seekval, "columns" )
lc_colname = columns->column
DO CASE
** Changed by JP, new operations
* CASE p_calc = "SUM"
CASE p_calc $ "SUM,AVG,STD,VAR"
** end change
REPLACE crosstab->&lc_colname WITH ;
&p_calfld + crosstab->&lc_colname
** Added by JP, ditto
IF p_calc # "SUM"
SELECT Number
GO recno("crosstab")
REPLACE &lc_colname WITH &lc_colname + 1
IF p_calc $ "STD,VAR"
SELECT Squares
GO recno("crosstab")
REPLACE &lc_colname WITH &lc_colname + ;
&p_calfld * &p_calfld
ENDIF STD or VAR
SELECT 1
ENDIF AVG, STD or VAR
** End of additions JP
CASE p_calc = "CNT"
REPLACE crosstab->&lc_colname WITH ;
1 + crosstab->&lc_colname
CASE p_calc = "MAX"
REPLACE crosstab->&lc_colname WITH ;
MAX( &p_calfld, crosstab->&lc_colname )
CASE p_calc = "MIN"
IF crosstab->&lc_colname = 0
REPLACE crosstab->&lc_colname WITH &p_calfld
ELSE
REPLACE crosstab->&lc_colname WITH ;
MIN( &p_calfld, crosstab->&lc_colname )
ENDIF
ENDCASE
ENDIF
ENDIF
ENDSCAN
** Next long section added by JP
** Do row and column summaries.
** In the same pass, use the extra tables to do AVG,STD and VAR calculations
** and place them in the Crosstab file.
** If you don't want summaries, remove this section
DECLARE ln_tarray [ln_columns + 2] && a variable per column of data
IF p_calc $ "AVG,STD,VAR" && including summary and unused side
DECLARE ln_narray[ln_columns + 2] && column for each table
IF p_calc # "AVG"
DECLARE ln_sqarray[ln_columns + 2]
ENDIF
ENDIF
ln_col = 2 && first column is side field names
DO WHILE ln_col <= ln_columns + 2 && initialize summary column too
STORE 0 TO ln_tarray[ ln_col ] && initialize at 0
IF p_calc $ "AVG,STD,VAR"
STORE 0 TO ln_narray[ ln_col ]
IF p_calc # "AVG"
STORE 0 TO ln_sqarray[ ln_col ]
ENDIF
ENDIF
ln_col = ln_col + 1
ENDDO
** end of part that may be removed if removing summaries
SELECT crosstab
SCAN && for each record
** take out all this if removing summaries
IF recno() = reccount() && except the last, which is totals
EXIT
ENDIF
** end of removable part
ln_col = 2
DO WHILE ln_col <= ln_columns + 1 && for each field except summary
lc_col = field( ln_col )
** remove next part too for no summaries
DO CASE
CASE p_calc $ "CNT,SUM"
ln_tarray[ ln_col ] = ln_tarray[ ln_col ] + &lc_col
ln_tarray[ ln_columns + 2 ] = ln_tarray[ ln_columns + 2 ] + &lc_col
CASE p_calc = "MAX"
ln_tarray[ ln_col ] = max( ln_tarray[ ln_col ], &lc_col )
ln_tarray[ ln_columns + 2 ] = ;
max( ln_tarray[ ln_columns + 2 ], &lc_col )
CASE p_calc = "MIN"
ln_tarray[ ln_col ] = min( ln_tarray[ ln_col ], &lc_col )
ln_tarray[ ln_columns + 2 ] = ;
min( ln_tarray[ ln_columns + 2 ], &lc_col )
OTHERWISE
** to here
SELECT Number
GO recno( "crosstab" )
ln_num = &lc_col
SELECT crosstab
IF ln_num = 0
REPLACE &lc_col WITH 0 && and don't count it
ELSE
** and remove from here
ln_tarray[ ln_col ] = ln_tarray[ ln_col ] + &lc_col
ln_tarray[ ln_columns + 2 ] = ln_tarray[ ln_columns + 2 ] + &lc_col
ln_narray[ ln_col ] = ln_narray[ ln_col ] + ln_num
ln_narray[ ln_columns + 2 ] = ln_narray[ ln_columns + 2 ] + ln_num
** to here
IF p_calc = "AVG"
REPLACE &lc_col WITH &lc_col / ln_num
ELSE
SELECT Squares
GO recno( "crosstab" )
ln_sq = &lc_col
SELECT crosstab
** and next two
ln_sqarray[ ln_col ] = ln_sqarray[ ln_col ] + ln_sq
ln_sqarray[ ln_columns + 2 ] = ;
ln_sqarray[ ln_columns + 2 ] + ln_sq
** down to here
ln_var = ( ln_sq - &lc_col * &lc_col / ln_num ) / ln_num
REPLACE &lc_col WITH iif( p_calc = "VAR", ln_var, sqrt( ln_var ) )
ENDIF STD or VAR
ENDIF zero occurrences
** and there won't be a case if no summaries so take out next line
ENDCASE
ln_col = ln_col + 1
ENDDO
** this all goes out too for no summaries
IF p_calc $ "AVG,STD,VAR"
SELECT Number
GO recno( "crosstab" )
REPLACE &lc_rtcol WITH ln_narray[ ln_columns + 2 ]
SELECT crosstab
IF ln_narray[ ln_columns + 2 ] = 0
REPLACE &lc_rtcol WITH 0
ELSE
IF p_calc = "AVG"
REPLACE &lc_rtcol WITH ln_tarray[ ln_columns + 2 ] ;
/ ln_narray[ ln_columns + 2 ]
ELSE
SELECT Squares
GO recno( "crosstab" )
REPLACE &lc_rtcol WITH ln_sqarray[ ln_columns + 2 ]
SELECT crosstab
ln_var = ( ln_sqarray[ ln_columns + 2 ] - ;
ln_tarray[ ln_columns + 2 ] * ;
ln_tarray[ ln_columns + 2 ] / ;
ln_narray[ ln_columns + 2 ] ) / ;
ln_narray[ ln_columns + 2 ]
REPLACE &lc_rtcol WITH iif(p_calc = "VAR", ln_var, sqrt( ln_var ))
ln_sqarray[ ln_columns + 2 ] = 0
ENDIF AVG
ENDIF 0 or not
ln_narray[ ln_columns + 2] = 0
ELSE
REPLACE &lc_rtcol WITH ln_tarray[ ln_columns + 2 ]
ENDIF AVG, STD or VAR
ln_tarray[ ln_columns + 2 ] = 0
** next line has to stay in
ENDSCAN
** Already at bottom record of crosstab - record column "totals"
** take out all the rest of the added routine for no summaries
ln_col = 2
DO WHILE ln_col <= ln_columns + 1
lc_col = field( ln_col )
IF p_calc $ "SUM,CNT,MAX,MIN"
REPLACE &lc_col WITH ln_tarray[ ln_col ]
DO CASE
CASE p_calc $ "SUM,CNT"
ln_tarray[ ln_columns + 2 ] = ;
ln_tarray[ ln_columns + 2 ] + ln_tarray[ ln_col ]
CASE p_calc = "MAX"
ln_tarray[ ln_columns + 2 ] = ;
max( ln_tarray[ ln_columns + 2 ], ln_tarray[ ln_col ] )
OTHERWISE
ln_tarray[ ln_columns + 2 ] = ;
min( ln_tarray[ ln_columns + 2 ], ln_tarray[ ln_col ] )
ENDCASE
ELSE
IF ln_narray[ ln_col ] = 0
REPLACE &lc_col WITH 0
ELSE
ln_tarray[ ln_columns + 2 ] = ;
ln_tarray[ ln_columns + 2 ] + ln_tarray[ ln_col ]
SELECT Number
GO recno( "crosstab" )
REPLACE &lc_col WITH ln_narray[ ln_col ]
ln_narray[ ln_columns + 2 ] = ;
ln_narray[ ln_columns + 2 ] + ln_narray[ ln_col ]
IF p_calc = "AVG"
SELECT crosstab
REPLACE &lc_col WITH ln_tarray[ ln_col ] / ln_narray[ ln_col ]
ELSE
SELECT Squares
GO recno( "crosstab" )
REPLACE &lc_col WITH ln_sqarray[ ln_col ]
ln_sqarray[ ln_columns + 2 ] = ;
ln_sqarray[ ln_columns + 2 ] + ln_sqarray[ ln_col ]
ln_var = ( ln_sqarray[ ln_col ] - ;
ln_tarray[ ln_col ] * ;
ln_tarray[ ln_col ] / ;
ln_narray[ ln_col ] ) / ;
ln_narray[ ln_col ]
SELECT crosstab
REPLACE &lc_col WITH iif( p_calc = "VAR", ln_var, sqrt( ln_var ) )
ENDIF not AVG
ENDIF zero occurrences
ENDIF easy operations
ln_col = ln_col + 1
ENDDO
* Grand "total"
DO CASE
CASE p_calc $ "SUM,CNT,MAX,MIN"
REPLACE &lc_rtcol WITH ln_tarray[ ln_columns + 2 ]
CASE p_calc = "AVG"
REPLACE &lc_rtcol WITH ln_tarray[ ln_columns + 2 ] / ;
ln_narray[ ln_columns + 2 ]
OTHERWISE
ln_var = ( ln_sqarray[ ln_columns + 2 ] - ;
ln_tarray[ ln_columns + 2 ] * ;
ln_tarray[ ln_columns + 2 ] / ;
ln_narray[ ln_columns + 2 ] ) / ;
ln_narray[ ln_columns + 2 ]
REPLACE &lc_rtcol WITH iif(p_calc = "VAR", ln_var, sqrt(ln_var))
ENDCASE
INDEX ON Seq TAG Seq
GO BOTTOM
REPLACE &lc_psidfld WITH "Summary"
SELECT 1
** End of long section added by JP; end that can be removed for no summaries.
@ 5, 50 SAY "Complete"
@ 6, 5 SAY lc_stat5 + + CHR(205) + CHR(205) + CHR(16)
@ 6, 50 SAY "Working"
*-- Remove the UNIQUE Tags on the data file
DELETE TAG topfld
DELETE TAG sidfld
*-- Put the CROSSTAB and columns into view
CLOSE DATABASE
SELECT 1
USE crosstab ORDER TAG Seq
USE columns IN 2 ORDER TAG column
*-- Create the field column labels
SET FIELDS TO &lc_psidfld. /R
SET FIELDS OFF
ln_col = 1
DO WHILE ln_col <= ln_columns
lc_colname = "COL" + SUBSTR( STR( 100 + ln_col, 3, 0), 2, 2 )
lc_coltitl = LOOKUP( columns->&lc_ptopfld, lc_colname, columns->column )
lc_coltitl = XT_ClnTl( (lc_coltitl) )
SET FIELDS TO &lc_coltitl = ( &lc_colname )
SET FIELDS OFF
ln_col = ln_col + 1
ENDDO
** Name the field for the row totals; last change by JP; remove for no summary
SET FIELDS TO Summary = &lc_rtcol
SET FIELDS ON
@ 6, 50 SAY "Complete"
DO XT_RestEv
RETURN
*-- EOP: Com_XTab